package org.hp.util;

import javax.sound.midi.Soundbank;
import java.sql.*;
import java.util.*;

public class DBHelper {
    public static void main(String[] args) {
        System.out.println("链接数据库："+getCon());
    }
    //常量大写
    private static final String URL="jdbc:mysql://127.0.0.1:3306/apartment?serverTimezone=UTC&characterEncoding=utf8";
    private static final String NAME="root";
    private static final String PASSWORD="root";
    private static final String DRIVER="com.mysql.cj.jdbc.Driver";
    private static Connection con=null;
    private static PreparedStatement ps=null;
    private static ResultSet rs=null;

    //加载数据库驱动
    static{
        try {
            Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接
    public static Connection getCon(){
        try {
            con= DriverManager.getConnection(URL,NAME,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return con;
    }

    /**
     * 查询  select  适用于所有    在servlet层更改sql皆可
     * @param sql
     * @param objs
     * @return
     */
    public List<Map> query(String sql,Object...objs){
        List<Object> objects = Arrays.asList(objs);
        List<Map> list=new ArrayList<>();
        con = DBHelper.getCon();
        try {
            ps = con.prepareStatement(sql);
            //赋予预编译对象的值
            for (int i = 0; i < (sql+" ").split("\\?").length - 1; i++) {
                ps.setObject(i + 1, objects.get(i));
            }
            rs = ps.executeQuery();
            ResultSetMetaData metaData=rs.getMetaData();
            while(rs.next()){
                Map map = new HashMap();
                //循环获取数据  这里的下标是从1开始
                for (int i = 1; i <= metaData.getColumnCount(); i++) {
                    String key=metaData.getColumnName(i);
                    map.put(key,rs.getObject(key));
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return list;
    }

    /**
     * 更新   适用于所有对数据进行更新 删除  增加
     * @param sql
     * @param objs
     * @return
     */
    public int renow(String sql,Object...objs){
        int num=0;
        con=DBHelper.getCon();
        //获取objs的值  在这里Object...objs表示objs可变，可有可无
        List<Object> objects=Arrays.asList(objs);
        try {
            ps=con.prepareStatement(sql);
            //赋予预编译的对象的值
            for (int i = 0; i < (sql+" ").split("\\?").length-1; i++) {
                ps.setObject(i+1,objects.get(i));
            }
            num=ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
        return num;
    }


    public int getCount(String sql, String... obj) {
        List<Map> list = query("select count(*) num from(" + sql + ") d", obj);
        return list != null && list.size() > 0 ? Integer.valueOf(list.get(0).get("num").toString()) : 0;
    }

    public PageBean<Map> pageList(int page, int limit ,String sql, String...obj) {
        //1.获取总条数
        int count = getCount(sql, obj);
        //2.根据总条数和每页条数进行计算总页数(根据是否有余数，判断是否需要页数加一)
        int totlePage = count%limit==0?count/limit:(count/limit)+1;
        //3.判断当前页是否大于总页数或小于1页
        if(page<1){
            page=1;
        }else if(page>totlePage){
            page=totlePage;
        }
        //4.判断通过后计算当前分页的起始位置
        int startindex=(page-1)*limit;
        sql+=" limit "+startindex+","+limit;
        List<Map> list = query(sql, obj);
        return new PageBean<Map>(totlePage, count, page, limit, list);
    }

    /**
     * 关闭资源
     */
    public static void close(){
        try {
            if(con!=null)   con.close();
            if(ps!=null)    ps.close();
            if(rs!=null)    rs.close();
            } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
